package com.javabean;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class jdbcAPI {
    ClassPathXmlApplicationContext applicationContext;
    JdbcTemplate jdbcTemplate;
    @Before
    public void before(){
        applicationContext=new ClassPathXmlApplicationContext("spring-jdbc.xml");
        jdbcTemplate= (JdbcTemplate) applicationContext.getBean("jdbc");
    }
    @After
    public void after(){
        applicationContext.close();
    }


    //使用jdbcTemplate完成数据库操作

    @Test
    public void insert(){
        String sql="insert into user(username,password,phone) values(?,?,?)";
        int update = jdbcTemplate.update(sql, "bz", "hbz", "dsdsb");
        System.out.println(update);
    }

    @Test
    public void update(){
        String sql="update `user` set username=? where uid=?";
        int update = jdbcTemplate.update(sql, "hbz",1);
        System.out.println(update);
    }

    @Test
    public void delete(){
        String sql="delete from `user` where uid=?";
        int update = jdbcTemplate.update(sql, 2);
        System.out.println(update);
    }

    @Test
    public void select(){
        String sql="select * from user";
        for (User user : jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class))) {
            System.out.println(user);
        }
    }
    @Test
    public void select2(){
        String sql="select uid id,username uname,password pwd,phone tel from user";

        //如果查询结果与属性没有自动映射 我们可以自己实现对应的映射关系
        RowMapper<User> rowMapper = new RowMapper<User>() {
            @Override
            //这个方法定义的是 每一行数据取出后怎么返回数据
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
               User u=new User();
                u.setUid(resultSet.getInt("id"));
                u.setUsername(resultSet.getString("uname"));
                u.setPassword(resultSet.getString("pwd"));
                u.setPhone(resultSet.getString("tel"));
               return u;
            }
        };
        for (User user : jdbcTemplate.query(sql, rowMapper)) {
            System.out.println(user);
        }
    }

    //聚合查询
    @Test
    public void testQueryCount(){
        Long count = jdbcTemplate.queryForObject("select count(*) from user", Long.class);
        System.out.println(count);
    }

    //查询一个
    @Test
    public void testQueryOne(){
        User u = jdbcTemplate.queryForObject("select * from user where username=?", new BeanPropertyRowMapper<>(User.class), "bz");
        System.out.println(u);
    }
}
